from sqlormx import db


create_table_sql = '''
CREATE TABLE IF NOT EXISTS person (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER,
  birth_date date DEFAULT NULL,
  sex smallint DEFAULT NULL,
  grade float DEFAULT NULL,
  point decimal(8,2) DEFAULT NULL,
  money decimal(8,4) DEFAULT NULL,
  create_by bigint DEFAULT NULL,
  create_time timestamp DEFAULT CURRENT_TIMESTAMP,
  update_by bigint DEFAULT NULL,
  update_time timestamp DEFAULT NULL,
  del_flag smallint NOT NULL DEFAULT 0
)
'''
select_key = 'SELECT last_insert_rowid()'


def full_test():
    print(db.save('person', name='zhangsan', age=54))
    print(db.save_sql_select_key(select_key, 'INSERT INTO person(name,age) VALUES(?,?)', 'lisi', 26))
    print(db.insert(table_name='person', name='wangwu', age=38))

    args = [
        {'name': '李四', 'age': 55, 'birth_date': '1968-10-08', 'sex': 0, 'grade': 1.0, 'point': 20.5, 'money': 854.56},
        {'name': '李四', 'age': 55, 'birth_date': '1968-10-08', 'sex': 0, 'grade': 1.0, 'point': 20.5, 'money': 854.56}
    ]
    db.batch_insert('person', *args)

    print(db.select('select * from person where name in(?,?)', 'zhangsan', 'lisi'))
    print(db.select_one('select * from person where name = ? limit 1', 'zhangsan'))

    print(db.query('select id, name, age from person'))
    print(db.query_one('select id, name, age from person where name=? limit 1', 'zhangsan'))

    print(db.get('select count(1) from person'))

    # print(db.select_page('select id, name, age from person limit ?,?', 1, 2))
    # print(db.query_page('select id, name, age from person', 1, 2))

    print(db.execute('delete from person where id = ?', 3))


if __name__ == '__main__':

    db.init('test.db', driver='sqlite3', show_sql=True, debug=True)
    #db.execute('drop table person')
    db.execute(create_table_sql)
    # db.execute('delete from person')
    db.truncate_table('person')
    full_test()
    print(db.query('PRAGMA table_info(person)'))
